package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.*;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.ArrivalRate;
import com.xmy.cultivate.entity.views.RecordCourseStudent;
import com.xmy.cultivate.entity.views.SchedulingClash;
import com.xmy.cultivate.entity.views.SignResult;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 排课详情 Mapper 接口
 * </p>
 *
 * @author min
 *
 * @since 2022-08-01
 */
public interface CourseSchedulingDetailMapper extends BaseMapper<CourseSchedulingDetail> {

    @Select("select a.*,b.course_id from course_scheduling_detail as a LEFT JOIN grade as b on a.grade_id = b.id  where a.id = #{id}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "teacherObj",
                    column = "teacher_id",
                    one = @One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(
                    property = "assistantObj",
                    column = "assistant_id",
                    one = @One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(
                    property = "classRoomObj",
                    column = "class_room_id",
                    one = @One(select = "com.xmy.cultivate.mapper.ClassRoomMapper.selectById")
            ),
            @Result(
                    property = "subjectsObj",
                    column = "subjects_id",
                    one = @One(select = "com.xmy.cultivate.mapper.SubjectsMapper.selectById")
            ),
            @Result(
                    property = "gradeObj",
                    column = "grade_id",
                    javaType = Grade.class,
                    one = @One(select = "com.xmy.cultivate.mapper.GradeMapper.selectById")
            ),
            @Result(
                    property = "courseObj",
                    column = "course_id",
                    javaType = Grade.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract CourseSchedulingDetail getOneById(@Param("id") Long id);

    @Select("SELECT * from student ${ew.customSqlSegment}")
    public abstract List<RecordCourseStudent> recordStudentList(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from (select a.*,b.status as grade_status,c.year_class_id from course_scheduling_detail as a LEFT JOIN grade as b on a.grade_id = b.id LEFT JOIN course as c on b.course_id = c.id) as t ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "teacherObj",
                    column = "teacher_id",
                    one = @One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(
                    property = "classRoomObj",
                    column = "class_room_id",
                    one = @One(select = "com.xmy.cultivate.mapper.ClassRoomMapper.selectById")
            ),
            @Result(
                    property = "subjectsObj",
                    column = "subjects_id",
                    one = @One(select = "com.xmy.cultivate.mapper.SubjectsMapper.selectById")
            ),
            @Result(
                    property = "gradeObj",
                    column = "grade_id",
                    one = @One(select = "com.xmy.cultivate.mapper.GradeMapper.selectById")
            ),
            @Result(
                    property = "schoolObj",
                    column = "schoolId",
                    one = @One(select = "com.xmy.cultivate.mapper.Organization.selectById")
            ),
            @Result(
                    property = "courseObj",
                    column = "course_id",
                    javaType = Grade.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract List<CourseSchedulingDetail> findAll(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select * from (select a.*,c.year_class_id from course_scheduling_detail as a LEFT JOIN grade as b on a.grade_id = b.id LEFT JOIN course as c on b.course_id = c.id) as t ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "teacherObj",
                    column = "teacher_id",
                    one = @One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(
                    property = "classRoomObj",
                    column = "class_room_id",
                    one = @One(select = "com.xmy.cultivate.mapper.ClassRoomMapper.selectById")
            ),
            @Result(
                    property = "subjectsObj",
                    column = "subjects_id",
                    one = @One(select = "com.xmy.cultivate.mapper.SubjectsMapper.selectById")
            ),
            @Result(
                    property = "gradeObj",
                    column = "grade_id",
                    one = @One(select = "com.xmy.cultivate.mapper.GradeMapper.selectById")
            ),
            @Result(
                    property = "schoolObj",
                    column = "schoolId",
                    one = @One(select = "com.xmy.cultivate.mapper.Organization.selectById")
            ),
            @Result(
                    property = "courseObj",
                    column = "course_id",
                    javaType = Grade.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract IPage<CourseSchedulingDetail> findAllForPage(IPage<?> page,@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 查找冲突排课
     * @param wrapper
     * @return
     */
    @Select("select * from (select school_id,grade_id,teacher_id,school_date,time_start,time_end,count(id) as count from course_scheduling_detail where year_part=${yearPart} and quarter=${quarterNum} GROUP BY grade_id,school_date,time_start,time_end) as t ${ew.customSqlSegment}")
    public abstract List<CourseSchedulingDetail> getFindClash(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("yearPart") Integer yearPart,@Param("quarterNum")Integer quarterNum);

    @Select("select * from course_scheduling_detail ${ew.customSqlSegment}")
    public abstract IPage<CourseSchedulingDetail> getListByClassId(IPage<?> page,@Param(Constants.WRAPPER)QueryWrapper queryWrapper);

    @Select("select * from course_scheduling_detail ${ew.customSqlSegment}")
    public abstract List<CourseSchedulingDetail> selectByGrade(@Param(Constants.WRAPPER)QueryWrapper queryWrapper);


    /**
     * 获取排课信息
     * @return
     */
    @Select("select * from scheduling_union_repair")
    public abstract List<RepairCourse> getSchedulingList();


    @Update("UPDATE course_scheduling_detail set deleted = 0 where id = #{id}")
    public boolean unDeleteForId(@Param("id") Long id);

    /**
     * 获取一条数据，包含软删除的数据
     * @param id
     * @return
     */
    @Select("select * from course_scheduling_detail where id = #{id} limit 1")
    public CourseSchedulingDetail getOneAndDeleted(@Param("id") Long id);


    /**
     * 获取排课与调课、补课、消课信息
     * @param queryWrapper
     * @return 返回数据，scheduling_type 1为 course_scheduling_detail 表里的排课 2为 repair_course表里的排课，course_type：课程类别：1精品课（正常排课），3补课，4调课，5消课,6缺课补课
     */
    @Select("select * from scheduling_union_repair ${ew.customSqlSegment}")
    public List<RepairCourse> getSchedulingUnionRepair(@Param(Constants.WRAPPER)QueryWrapper queryWrapper);


    @Select({"<script>",
            "SELECT * FROM (",
                    "SELECT",
                    "t1.`id` AS `id`,",
                    "t1.`school_id`,",
                    "t1.`grade_id`,",
                    "t1.`grade_name`,",
                    "t1.`teacher_id`,",
                    "t1.`teacher_name`,",
                    "t1.`school_date`,",
                    "t1.`start_date_time`,",
                    "t1.`end_date_time`,",
                    "t1.`time_end`,",
                    "t1.`time_start`,",
                    "t1.`deleted`,",
                    "t1.`year_part`,",
                    "t1.`quarter`,",
                    "t1.`subjects_id`,",
                    "t1.`subjects_name`,",
                    "t1.`lesson_type`,(",
                    "CASE",
                        "t1.`type` ",
                        "WHEN 2 THEN",
                        "4 ELSE 1 ",
                      "END ",
                      ") AS `course_type`,",
                      "t3.`student_id`,",
                      "1 AS `scheduling_type` ",
                    "FROM",
                      "`course_scheduling_detail` AS t1",
                      "INNER JOIN student_grade AS t3 ON t1.grade_id = t3.grade_id",
                      "LEFT JOIN repair_course AS t4 ON t1.id = t4.out_scheduling_detail_id ",
                      "AND t4.student_id = t3.student_id ",
                      "AND t4.year_part = t3.year_part ",
                      "AND t4.`quarter` = t3.quarter_num ",
                      "AND t4.deleted = 0 ",
                      "AND t4.course_type = 4 ",
                    "WHERE",
                      "(",
                        "t1.`deleted` = 0 ",
                        "and t1.`status` &lt;&gt; 2 ",
                        "AND t1.school_date = '${schoolDate}' ",
                        "AND t3.STATUS = 1 ",
                        "AND t3.reading_status in(${readingStatus}) ",
                        "AND t3.year_part = ${yearPart} ",
                        "AND t3.quarter_num = ${quarterNum} ",
                        "AND t3.student_id = ${studentId} ",
                        "AND t4.id IS NULL ",
                      ") UNION",
                    "SELECT",
                      "t2.`id` AS `id`,",
                      "t2.`school_id`,",
                      "t2.`grade_id`,",
                      "t2.`grade_name`,",
                      "t2.`teacher_id`,",
                      "t2.`teacher_name`,",
                      "t2.`school_date`,",
                      "t2.`start_date_time`,",
                      "t2.`end_date_time`,",
                      "t2.`time_end`,",
                      "t2.`time_start`,",
                      "t2.`deleted`,",
                      "t2.`year_part`,",
                      "t2.`quarter`,",
                      "t2.`subjects_id`,",
                      "t2.`subjects_name`,",
                      "t2.`lesson_type`,",
                      "t2.`course_type`,",
                      "t2.`student_id`,",
                      "2 AS `scheduling_type` ",
                    "FROM",
                      "`repair_course` AS t2 ",
                    "WHERE",
                      "t2.`deleted` = 0 ",
                      "and t2.`status` &lt;&gt; 2 ",
                      "AND t2.school_date = '${schoolDate}' ",
                      "AND t2.year_part = ${yearPart} ",
                    "AND t2.`QUARTER` = ${quarterNum} ",
                    "AND t2.student_id = ${studentId}",
            ") AS t where 1=1",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                "and teacher_id = ${teacherId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and subjects_id = ${subjectsId}",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                "and lesson_type = ${lessonType}",
            "</when>",
            "ORDER BY start_date_time asc",
    "</script>"})
    public List<RepairCourse> getSchedulingAndRepair(@Param("yearPart") Integer yearPart,
                                                       @Param("quarterNum") Integer quarterNum,
                                                       @Param("teacherId") Long teacherId,
                                                       @Param("studentId") String studentId,
                                                       @Param("schoolDate") String schoolDate,
                                                       @Param("subjectsId") String subjectsId,
                                                       @Param("lessonType") Integer lessonType,
                                                       @Param("readingStatus") String readingStatus);

    @Select("select count(id) as count from student_sign_give where `year_part` = ${yearPart} and quarter_num = ${quarterNum} and '${dateNow}' BETWEEN start_date and end_date")
    public Integer getSignGiveCount(@Param("yearPart") Integer yearPart,
                                    @Param("quarterNum") Integer quarterNum,
                                    @Param("dateNow") String dateNow);


    @Select({"<script>",
            "select * from scheduling_union_repair",
            "where",
            "1=1",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and school_id = #{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "and grade_id = #{gradeId}",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "and teacher_id = #{teacherId}",
            "</when>",
            "<when test='schoolDate!=null and schoolDate!=&apos;&apos; '>",
            "and school_date = #{schoolDate}",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "and lesson_type = ${lessonType}",
            "</when>",
            "GROUP BY time_start,time_end",
            "ORDER BY time_start,time_end",
            "</script>"}
    )
    public List<RepairCourse> getSchoolDateList(@Param("schoolId") String schoolId,@Param("gradeId") String gradeId,@Param("teacherId") String teacherId,@Param("schoolDate") String schoolDate,@Param("lessonType") String lessonType);


    @Select("select * from course_scheduling_detail where id = ${id}")
    public CourseSchedulingDetail getCourseSchedulingDetailById(@Param("id") Long id);

    @Select("DELETE from course_scheduling_detail where grade_id = ${gradeId} and arranging_way = 3 and id not in(select scheduling_detail_id from record_course where grade_id = ${gradeId} and deleted = 0 and scheduling_detail_id is not null)")
    public Long deleteForGradeId(@Param("gradeId") Long gradeId,@Param("dateTime") LocalDateTime dateTime);

    @Select("select count(id) as count from course_scheduling_detail where id in (select scheduling_detail_id from record_course where deleted=0 and grade_id = ${gradeId})")
    public Integer getSchedulingCountForRecord(@Param("gradeId") Long gradeId);


    //修改为按课次判断，取消按日期 and school_date = #{schoolDate}
    @Select("select count(id) as count from course_scheduling_detail where grade_id = ${gradeId} and lesson_num = ${lessonNum} and arranging_way = 3 and deleted = 0")
    public Long getIsAt(@Param("gradeId")Long gradeId,@Param("lessonNum") Integer lessonNum);

    @Select("select count(id) as count from course_scheduling_detail where grade_id = ${gradeId} and school_date = #{schoolDate} and deleted = 0")
    public Long getIsAtForDate(@Param("gradeId")Long gradeId,@Param("schoolDate") LocalDate schoolDate);


    @Select({"<script>",
            "SELECT",
                    "t.*,",
                    "t5.`name` AS student_name,",
                    "getSubjectsName ( t.subjects_id ) AS subjects_name ",
                "FROM",
                    "(",
                    "SELECT",
                        "t2.id,",
                        "t1.student_id,",
                        "t2.school_id,",
                        "t4.teacher_id,",
                        "getStaffNameForId ( t4.teacher_id ) AS teacher_name,",
                        "t1.grade_id,",
                        "t4.`name` AS grade_name,",
                        "t2.subjects_id,",
                        "( CASE t2.`type` WHEN 2 THEN 4 ELSE 1 END ) AS course_type,",
                        "t2.lesson_type,",
                        "getSchoolName ( t2.school_id ) AS school_name,",
                        "t2.start_date_time,",
                        "t2.end_date_time",
                    "FROM",
                        "(",
                        "SELECT",
                        "* ",
                        "FROM",
                        "course_scheduling_detail ",
                        "WHERE",
                        "year_part = ${yearPart} ",
                        "AND `QUARTER` = ${quarterNum} ",
                        "AND deleted = 0 ",
                        "AND school_date = #{schoolDate} ",
                        "AND deleted = 0 ",
                        ") AS t2",
                    "INNER JOIN (",
                    "SELECT",
                        "* ",
                    "FROM",
                        "student_grade ",
                    "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum} ",
                        "AND `STATUS` = 1 ",
                        "AND deleted = 0 ",
                        "AND reading_status = 1 ",
                    "GROUP BY",
                        "school_id,",
                        "student_id,",
                        "subjects_id,",
                        "lesson_type ",
                    ") AS t1 ON t1.grade_id = t2.grade_id",
                    "LEFT JOIN grade AS t4 ON t1.grade_id = t4.id",
                    "LEFT JOIN ( SELECT * FROM repair_course WHERE deleted = 0 ) AS tr1 ON tr1.out_scheduling_detail_id = t2.id ",
                        "AND t1.student_id = tr1.student_id ",
                    "WHERE",
                        "tr1.id IS NULL UNION ALL",
                    "SELECT",
                        "id,",
                        "student_id,",
                        "rep.school_id,",
                        "teacher_id,",
                        "teacher_name,",
                        "rep.grade_id,",
                        "grade_name,",
                        "subjects_id,",
                        "course_type,",
                        "lesson_type,",
                        "getSchoolName ( rep.school_id ) AS school_name,",
                        "start_date_time,",
                        "end_date_time ",
                    "FROM",
                        "repair_course AS rep ",
                    "WHERE",
                        "year_part = ${yearPart} ",
                        "AND `QUARTER` = ${quarterNum} ",
                        "AND rep.deleted = 0 ",
                        "AND school_date = #{schoolDate} ",
                    ") AS t",
                    "LEFT JOIN student AS t5 ON t.student_id = t5.id",
                "WHERE",
                    "1 = 1 ",
                    "AND t.school_id = ${schoolId} and t.student_id = ${studentId}",
            "</script>"}
    )
    public List<RepairCourse> getStudentSchedulingList(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("schoolDate") String schoolDate, @Param("lessonType") String lessonType);



    @Select({"<script>",
            "SELECT",
                "*,(",
                "studentAllCount - arrivedCount ",
                ") AS noArrivedCount ",
            "FROM",
                "(",
                "SELECT",
                    "*,",
                    "count(*) AS studentAllCount,",
                    "sum( CASE WHEN STATUS = 1 THEN 1 ELSE 0 END ) AS arrivedCount ",
                "FROM",
                "(",
                "SELECT",
                "t.*,",
                "t5.`name` AS student_name ",
                "FROM",
                "(",
                "SELECT",
                "t1.student_id,",
                "t2.school_id,",
                "t4.teacher_id,",
                "getStaffNameForId(t4.teacher_id) as teacher_name,",
                "t1.grade_id,",
                "t4.`name` AS grade_name,",
                "t2.subjects_id,",
                "t2.lesson_type,",
                "IFNULL(( SELECT `status` FROM record_course WHERE t1.student_id = student_id AND scheduling_detail_id = t2.id AND deleted = 0 ), 2 ) AS `status`,",
                "getSchoolName ( t2.school_id ) AS school_name ",
                "FROM",
                "(",
                "SELECT",
                "* ",
                "FROM",
                "course_scheduling_detail ",
                "WHERE",
                "year_part = ${yearPart} ",
                "AND `quarter` = ${quarterNum} ",
                "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                "AND school_date &gt;= #{startDate} ",
                "</when>",
                "<when test='endDate!=null and endDate!=&apos;&apos;'>",
                "AND school_date &lt;= #{endDate} ",
                "</when>",
                "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                "and time_start &gt;=#{timeStart}",
                "</when>",
                "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                "and time_end &lt;=#{timeEnd}",
                "</when>",
                "AND deleted = 0 ",
                ") AS t2",
                "INNER JOIN (",
                "SELECT",
                "* ",
                "FROM",
                "student_grade ",
                "WHERE",
                "year_part = ${yearPart} ",
                "AND quarter_num = ${quarterNum} ",
                "AND `status` = 1 ",
                "AND reading_status = 1 ",
                "AND deleted = 0 ",
                "<if test= 'isHistory == false'>",
                "AND reading_status = 1 ",
                "</if>",
                "<if test= 'isHistory == true'>",
                "AND is_last = 1 ",
                "</if>",
                "GROUP BY",
                "school_id,",
                "student_id,",
                "subjects_id,",
                "lesson_type ",
                ") AS t1 ON t1.grade_id = t2.grade_id",
                "LEFT JOIN grade AS t4 ON t1.grade_id = t4.id ",
                "WHERE",
                "t1.created_at &lt;= t2.end_date_time ",
                "AND t1.student_id NOT IN ( SELECT student_id FROM repair_course WHERE deleted = 0 AND out_scheduling_detail_id = t2.id AND course_type = 4 ) UNION ALL",
                "SELECT",
                "student_id,",
                "rep.school_id,",
                "teacher_id,",
                "teacher_name,",
                "rep.grade_id,",
                "grade_name,",
                "subjects_id,",
                "lesson_type,",
                "IFNULL(( SELECT `status` FROM record_course WHERE rep.student_id = student_id AND scheduling_detail_id = rep.id AND deleted = 0 ), 2 ) AS `status`,",
                "getSchoolName ( rep.school_id ) AS school_name ",
                "FROM",
                "repair_course AS rep ",
                "WHERE",
                "year_part = ${yearPart} ",
                "AND `quarter` = ${quarterNum} ",
                "AND rep.deleted = 0 ",
                "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                "AND school_date &gt;= #{startDate} ",
                "</when>",
                "<when test='endDate!=null and endDate!=&apos;&apos;'>",
                "AND school_date &lt;= #{endDate} ",
                "</when>",
                "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                "and time_start &gt;=#{timeStart}",
                "</when>",
                "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                "and time_end &lt;=#{timeEnd}",
                "</when>",
                ") AS t",
                "LEFT JOIN student AS t5 ON t.student_id = t5.id ",
                ") AS tt ",
                "where 1=1",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "and subjects_id=${subjectsId}",
                "</when>",
                "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                "AND `lesson_type` = ${lessonType} ",
                "</when>",
                "<if test= 'type == 3'>",
                "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
                "</if>",
                "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; '>",
                "and school_id in(${schoolIdStr})",
                "</when>",
                "GROUP BY",
                "<if test= 'type == 1 or type == 3'>",
                "teacher_id",
                "</if>",
                "<if test= 'type == 2'>",
                "school_id,teacher_id",
                "</if>",
                "<if test= 'type == 4'>",
                "school_id",
                "</if>",
                ") AS tt2",
            "</script>"})
    public abstract List<ArrivalRate> arrivalRate(@Param("schoolIdStr") String schoolIdStr, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("type") String type, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("timeStart")String timeStart, @Param("timeEnd")String timeEnd, @Param("lessonType")Integer lessonType, @Param("isHistory")boolean isHistory);

    @Select({"<script>",
            "SELECT",
                "t.*,t5.`name` as student_name ",
            "FROM",
                "(",
                "SELECT",
                    "t1.student_id,",
                    "t2.school_id,",
                    "t4.teacher_id,",
                    "getStaffNameForId(t4.teacher_id) as teacher_name,",
                    "t1.grade_id,",
                    "t4.`name` AS grade_name,",
                    "t2.subjects_id,",
                    "t2.lesson_type,",
                    "IFNULL(( SELECT `status` FROM record_course WHERE t1.student_id = student_id AND scheduling_detail_id = t2.id AND deleted = 0 ), 2 ) AS `status`,",
                    "getSchoolName ( t2.school_id ) AS school_name ",
                "FROM",
                    "(",
                    "SELECT",
                        "* ",
                    "FROM",
                        "course_scheduling_detail ",
                    "WHERE",
                        "year_part = ${yearPart} ",
                        "AND `quarter` = ${quarterNum} ",
                        "AND deleted = 0 ",
                        "AND school_date &gt;= #{startDate} ",
                        "AND school_date &lt;= #{endDate} ",
                        "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                        "and time_start &gt;=#{timeStart}",
                        "</when>",
                        "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                        "and time_end &lt;=#{timeEnd}",
                        "</when>",
                        "AND deleted = 0 ",
                    ") AS t2",
                    "INNER JOIN (",
                    "SELECT",
                        "* ",
                    "FROM",
                        "student_grade ",
                    "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum} ",
                        "AND `status` = 1 ",
                        "AND deleted = 0 ",
                        "<if test= 'isHistory == false'>",
                        "AND reading_status = 1 ",
                        "</if>",
                        "<if test= 'isHistory == true'>",
                        "AND is_last = 1 ",
                        "</if>",
                    "GROUP BY",
                        "school_id,",
                        "student_id,",
                        "subjects_id,",
                        "lesson_type ",
                    ") AS t1 ON t1.grade_id = t2.grade_id",
                    "LEFT JOIN grade AS t4 ON t1.grade_id = t4.id ",
                "WHERE",
                    "t1.created_at &lt;= t2.end_date_time ",
                    "AND t1.student_id NOT IN ( SELECT student_id FROM repair_course WHERE deleted = 0 AND out_scheduling_detail_id = t2.id AND course_type = 4 )",
                "UNION ALL",
                "SELECT",
                    "student_id,",
                    "rep.school_id,",
                    "teacher_id,",
                    "teacher_name,",
                    "rep.grade_id,",
                    "grade_name,",
                    "subjects_id,",
                    "lesson_type,",
                    "IFNULL(( SELECT `status` FROM record_course WHERE rep.student_id = student_id AND scheduling_detail_id = rep.id AND deleted = 0 ), 2 ) AS `status`,",
                    "getSchoolName ( rep.school_id ) AS school_name ",
                "FROM",
                    "repair_course AS rep ",
                "WHERE",
                    "year_part = ${yearPart} ",
                    "AND `quarter` = ${quarterNum} ",
                    "AND rep.deleted = 0 ",
                    "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                    "AND school_date &gt;= #{startDate} ",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                    "AND school_date &lt;= #{endDate} ",
                    "</when>",
                    "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                    "and time_start &gt;=#{timeStart}",
                    "</when>",
                    "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                    "and time_end &lt;=#{timeEnd}",
                    "</when>",
                ") AS t",
                "LEFT JOIN student AS t5 ON t.student_id = t5.id",
            "where 1=1",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and t.school_id =${schoolId}",
                "</when>",
                "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                "AND t.`lesson_type` = ${lessonType} " ,
                "</when>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t.teacher_id = ${teacherId}" ,
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t.subjects_id =${subjectsId}",
                "</when>",
                "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t.`grade_id` = ${gradeId} " ,
                "</when>",
                "<when test='status!=null and status!=&apos;&apos; '>",
                "and t.status=${status}",
                "</when>",
            "</script>"})
    public abstract List<RecordCourse> arrivalRateDetail(@Param("schoolId")String schoolId,@Param("subjectsId")String subjectsId,@Param("yearPart")String yearPart,@Param("quarterNum")String quarterNum,@Param("startDate")String startDate,@Param("endDate")String endDate,@Param("gradeId")String gradeId,@Param("teacherId")String teacherId,@Param("type")String type,@Param("status")String status,@Param("timeStart")String timeStart,@Param("timeEnd")String timeEnd,@Param("lessonType")Integer lessonType,@Param("isHistory")boolean isHistory);

    /**
     * 签到查看记录
     * @param schoolId
     * @param subjectsId
     * @param yearPart
     * @param quarterNum
     * @param startDate
     * @param endDate
     * @param gradeId
     * @param teacherId
     * @param type
     * @param status
     * @param timeStart
     * @param timeEnd
     * @param lessonType
     * @param isHistory
     * @return
     */
    @Select({"<script>",
            "SELECT",
                "t.*,t5.`name` as name,",
                "sg.course_type_name,",
                "IF(t6.out_date_time IS NULL,t6.in_date_time,t6.out_date_time) AS sign_date_time,",
                "IF(IF(t6.out_oss_image IS NULL,t6.oss_image,t6.out_oss_image) IS NULL,t5.head_img,IF(t6.out_oss_image IS NULL,t6.oss_image,t6.out_oss_image)) AS image_url,",
                "IF(t6.out_date_time IS NULL,1,2) AS sign_type,",
                "t5.linkman_phone,",
                "IF(t.`status` =1,1,0) as sign_status",
            "FROM",
                "(",
                "SELECT",
                    "t2.id,",
                    "t2.year_part,",
                    "t2.`quarter` as quarter_num,",
                    "t1.student_id,",
                    "t2.school_id,",
                    "t4.teacher_id,",
                    "getStaffNameForId(t4.teacher_id) as teacher_name,",
                    "t1.grade_id,",
                    "t4.`name` AS grade_name,",
                    "t2.subjects_id,",
                    "t2.lesson_type,",
                    "IFNULL(( SELECT `status` FROM record_course WHERE t1.student_id = student_id AND scheduling_detail_id = t2.id AND deleted = 0 ), 0 ) AS `status`,",
                    "getSchoolName ( t2.school_id ) AS school_name ",
                "FROM",
                    "(",
                    "SELECT",
                        "* ",
                    "FROM",
                        "course_scheduling_detail ",
                    "WHERE",
                        "year_part = ${yearPart} ",
                        "AND `quarter` = ${quarterNum} ",
                        "AND school_date &gt;= #{startDate} ",
                        "AND school_date &lt;= #{endDate} ",
                        " <when test='startAndEndList != null'> ",
                        "AND (",
                            "<foreach collection='startAndEndList' item='timeItem' open='(' separator='or' close=')'>",
                                "time_start &gt;=#{timeItem.timeStart}",
                                "and time_end &lt;=#{timeItem.timeEnd}",
                            "</foreach>",
                        ")",
                        " </when> ",
                        "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                        "and time_start &gt;=#{timeStart}",
                        "</when>",
                        "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                        "and time_end &lt;=#{timeEnd}",
                        "</when>",
                        "AND deleted = 0 ",
                        "AND status &lt;&gt; 2 ",
                    ") AS t2",
                    "INNER JOIN (",
                    "SELECT",
                        "* ",
                    "FROM",
                        "student_grade ",
                    "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum} ",
                        "AND `status` = 1 ",
                        "AND deleted = 0 ",
                        "<if test= 'isHistory == false'>",
                        "AND reading_status in(${readingStatus}) ",
                        "</if>",
                        "<if test= 'isHistory == true'>",
                        "AND is_last = 1 ",
                        "</if>",
                    "GROUP BY",
                        "school_id,",
                        "grade_id,",
                        "student_id,",
                        "subjects_id,",
                        "lesson_type ",
                    ") AS t1 ON t1.grade_id = t2.grade_id",
                    "LEFT JOIN grade AS t4 ON t1.grade_id = t4.id ",
                    "LEFT JOIN (select * from repair_course where deleted=0) as tr1 on  tr1.out_scheduling_detail_id = t2.id and t1.student_id = tr1.student_id",
            "WHERE",
                    "t1.created_at &lt;= t2.end_date_time ",
            "and tr1.id IS NULL",
                "UNION ALL",
                "SELECT",
                    "id,",
                    "year_part,",
                    "`quarter` as quarter_num,",
                    "student_id,",
                    "rep.school_id,",
                    "teacher_id,",
                    "teacher_name,",
                    "rep.grade_id,",
                    "grade_name,",
                    "subjects_id,",
                    "lesson_type,",
                    "IFNULL(( SELECT `status` FROM record_course WHERE rep.student_id = student_id AND scheduling_detail_id = rep.id AND deleted = 0 ), 0 ) AS `status`,",
                    "getSchoolName ( rep.school_id ) AS school_name ",
                "FROM",
                    "repair_course AS rep ",
                "WHERE",
                    "year_part = ${yearPart} ",
                    "AND `quarter` = ${quarterNum} ",
                    "AND rep.deleted = 0 ",
                    " <when test='startAndEndList != null'> ",
                    "AND (",
                        "<foreach collection='startAndEndList' item='timeItem' open='(' separator='or' close=')'>",
                        "time_start &gt;=#{timeItem.timeStart}",
                        "and time_end &lt;=#{timeItem.timeEnd}",
                        "</foreach>",
                    ")",
                    " </when> ",
                    "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                    "AND school_date &gt;= #{startDate} ",
                    "</when>",
                    "<when test='startDate!=null and startDate!=&apos;&apos;'>",
                    "AND school_date &lt;= #{endDate} ",
                    "</when>",
                    "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                    "and time_start &gt;=#{timeStart}",
                    "</when>",
                    "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                    "and time_end &lt;=#{timeEnd}",
                    "</when>",
                ") AS t",
                "LEFT JOIN (select student_grade.*,grade.course_type_id,course_type.name as course_type_name from student_grade LEFT JOIN grade on student_grade.grade_id = grade.id LEFT JOIN course_type on grade.course_type_id = course_type.id where student_grade.deleted=0 and student_grade.is_last = 1 and student_grade.lesson_type =1) as sg on t.year_part = sg.year_part and t.quarter_num = sg.quarter_num and t.subjects_id = sg.subjects_id and t.student_id = sg.student_id",
                "LEFT JOIN student AS t5 ON t.student_id = t5.id",
                "LEFT JOIN ( SELECT * FROM student_sign_in ) AS t6 ON t6.scheduling_detail_id = t.id AND t6.student_id = t.student_id",
            "where 1=1",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and t.school_id =${schoolId}",
                "</when>",
                "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                "AND t.`lesson_type` = ${lessonType} " ,
                "</when>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t.teacher_id = ${teacherId}" ,
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t.subjects_id =${subjectsId}",
                "</when>",
                "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t.`grade_id` = ${gradeId} " ,
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t.`course_type_id` = ${classTypeId} " ,
                "</when>",
                "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND t5.`name` like '%${studentName}%' " ,
                "</when>",
                "<when test='status!=null and status!=&apos;&apos; and status == 1 '>",
                    "and t.status=${status}",
                "</when>",
                "<when test='status!=null and status!=&apos;&apos; and status == 0 '>",
                    "and t.status&lt;&gt;1",
                "</when>",
            "</script>"})
    public abstract List<SignResult> signInDetail(@Param("schoolId")String schoolId, @Param("subjectsId")String subjectsId, @Param("yearPart")String yearPart, @Param("quarterNum")String quarterNum, @Param("startDate")String startDate, @Param("endDate")String endDate, @Param("gradeId")String gradeId, @Param("teacherId")String teacherId, @Param("type")String type, @Param("status")String status, @Param("timeStart")String timeStart, @Param("timeEnd")String timeEnd, @Param("lessonType")Integer lessonType,@Param("classTypeId") String classTypeId,@Param("studentName") String studentName, @Param("isHistory")boolean isHistory,@Param("startAndEndList") List<RecordCourse> startAndEndList,@Param("readingStatus") String readingStatus);

    /**
     * 排课冲突
     * @return
     */
    @Select({"<script>",
                "SELECT",
                    "*,",
                    "getSchoolName ( school_id ) AS school_name,",
                    "getSubjectsName ( subjects_id ) AS subjects_name,",
                    "getGradeNameForId ( grade_id ) AS grade_name,",
                    "getStudentNameForId ( student_id ) AS student_name ",
                "FROM",
                    "(",
                    "SELECT",
                        "b.id AS scheduling_id,",
                        "a.student_id,",
                        "a.school_id,",
                        "a.subjects_id,",
                        "a.lesson_type,",
                        "a.grade_id,",
                        "b.time_start,",
                        "b.time_end,",
                        "( CASE `b`.`type` WHEN 2 THEN 4 ELSE 1 END ) AS `course_type`,",
                        "1 AS `scheduling_type` ",
                    "FROM",
                        "(",
                            "SELECT",
                                "* ",
                            "FROM",
                                "student_grade ",
                            "WHERE",
                                "deleted = 0 ",
                                "AND `STATUS` = 1 ",
                                "AND reading_status = 1 ",
                                "AND student_id in(${studentIdStr}) ",
                                "AND year_part = ${yearPart}",
                                "AND `quarter_num` = ${quarterNum} ",
                        ") AS a",
                        "LEFT JOIN course_scheduling_detail AS b ON a.grade_id = b.grade_id ",
                        "LEFT JOIN (select * from repair_course where deleted=0) as tr1 on  tr1.out_scheduling_detail_id = b.id and a.student_id = tr1.student_id",
                    "WHERE",
                        "( b.start_date_time BETWEEN #{startDate} AND #{endDate} OR b.end_date_time BETWEEN #{startDate} AND #{endDate} OR (b.start_date_time&lt;= #{startDate} and b.end_date_time &gt;=#{endDate})) ",
                        "and tr1.id IS NULL",
                        "AND b.deleted = 0 ",
                        "AND b.status &lt;&gt;2 ",
                        "AND b.year_part = ${yearPart} ",
                        "AND b.`quarter` = ${quarterNum}",
                        "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                        "and b.lesson_type =${lessonType}",
                        "</when>",
                        "<when test='outSchedulingId!=null and outSchedulingId!=&apos;&apos; '>",
                        "and b.id &lt;&gt; ${outSchedulingId}",
                        "</when>",
                        "GROUP BY",
                        "a.grade_id,",
                        "a.student_id",
                    "UNION",
                    "SELECT",
                        "id AS scheduling_id,",
                        "student_id,",
                        "school_id,",
                        "subjects_id,",
                        "lesson_type,",
                        "grade_id,",
                        "time_start,",
                        "time_end,",
                        "course_type,",
                        "2 AS `scheduling_type`",
                    "FROM",
                        "repair_course ",
                    "WHERE",
                        "deleted = 0 ",
                        "AND student_id in(${studentIdStr}) ",
                        "AND year_part = ${yearPart} ",
                        "AND `quarter` = ${quarterNum} ",
                        "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                            "and lesson_type =${lessonType}",
                        "</when>",
                        "AND ( start_date_time BETWEEN #{startDate} AND #{endDate} OR end_date_time BETWEEN #{startDate} AND #{endDate} OR (start_date_time&lt;= #{startDate} and end_date_time &gt;=#{endDate})) ",
                    ") AS t1",
            "</script>"})
    public abstract List<SchedulingClash> schedulingClash(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("studentIdStr") String studentIdStr, @Param("startDate") String startDateTime, @Param("endDate") String endDateTime, @Param("lessonType") String lessonType,@Param("outSchedulingId") String outSchedulingId);



    /**
     * 排课冲突
     * @return
     */
    @Select({"<script>",
                "SELECT",
                    "count(*) as count",
                "FROM",
                    "(",
                    "SELECT",
                        "b.id AS scheduling_id,",
                        "a.student_id,",
                        "a.school_id,",
                        "a.subjects_id,",
                        "a.lesson_type,",
                        "a.grade_id ",
                    "FROM",
                        "(",
                            "SELECT",
                                "* ",
                            "FROM",
                                "student_grade ",
                            "WHERE",
                                "deleted = 0 ",
                                "AND `STATUS` = 1 ",
                                "AND reading_status = 1 ",
                                "AND student_id = ${studentId} ",
                                "AND year_part = ${yearPart}",
                                "AND `quarter_num` = ${quarterNum} ",
                        ") AS a",
                        "LEFT JOIN course_scheduling_detail AS b ON a.grade_id = b.grade_id ",
                        "LEFT JOIN (select * from repair_course where deleted=0) as tr1 on  tr1.out_scheduling_detail_id = b.id and a.student_id = tr1.student_id",
                    "WHERE",
                        "( b.start_date_time BETWEEN #{startDate} AND #{endDate} OR b.end_date_time BETWEEN #{startDate} AND #{endDate} OR (b.start_date_time&lt;= #{startDate} and b.end_date_time &gt;=#{endDate})) ",
                        "and tr1.id IS NULL",
                        "AND b.deleted = 0 ",
                        "AND b.status &lt;&gt;2 ",
                        "AND b.year_part = ${yearPart} ",
                        "AND b.`quarter` = ${quarterNum}",
                        "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                        "and b.lesson_type =${lessonType}",
                        "</when>",
                    "UNION",
                    "SELECT",
                        "id AS scheduling_id,",
                        "student_id,",
                        "school_id,",
                        "subjects_id,",
                        "lesson_type,",
                        "grade_id ",
                    "FROM",
                        "repair_course ",
                    "WHERE",
                        "student_id = ${studentId} ",
                        "AND deleted = 0 ",
                        "AND year_part = ${yearPart} ",
                        "AND `quarter` = ${quarterNum} ",
                        "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                            "and lesson_type =${lessonType}",
                        "</when>",
                        "AND ( start_date_time BETWEEN #{startDate} AND #{endDate} OR end_date_time BETWEEN #{startDate} AND #{endDate} OR (start_date_time&lt;= #{startDate} and end_date_time &gt;=#{endDate})) ",
                    ") AS t1",
            "</script>"})
    public abstract Long schedulingClashCount(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonType") String lessonType);


    @Select("select * from course_scheduling_detail where `year_part` = ${yearPart} and `quarter` = ${quarterNum} and deleted = 0 and lesson_type = ${lessonType} GROUP BY grade_id ORDER BY school_id asc")
    public abstract List<CourseSchedulingDetail> getSchedulingGradeList(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("lessonType") Integer lessonType);

    /**
     * 获取班级排课的最大课次+1（即下一次课次）
     * @param gradeId
     * @return
     */
    @Select("select (IFNULL(MAX(lesson_num),0)+1) as lesson_num from course_scheduling_detail where deleted=0 and grade_id = ${gradeId} and school_date <= #{schoolDate}")
    public abstract Integer getNextLessonNum(@Param("gradeId") Long gradeId,@Param("schoolDate") LocalDate schoolDate);

    @Select("select MIN(school_date) as school_date from course_scheduling_detail where deleted=0 and grade_id = ${gradeId}")
    public abstract LocalDate getGradeMinDate(@Param("gradeId") Long gradeId);

    @Select("select MAX(school_date) as school_date from course_scheduling_detail where deleted=0 and grade_id = ${gradeId}")
    public abstract LocalDate getGradeMaxDate(@Param("gradeId") Long gradeId);


    /**
     * 获取学生的排课列表
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @param startDateTime
     * @return
     */
    @Select({
            "<script>",
                "select * from course_scheduling",
                "where",
                "grade_id IN ( SELECT grade_id FROM student_grade WHERE STATUS = 1 AND reading_status = 1 AND year_part = ${yearPart} AND quarter_num = ${quarterNum} AND student_id = ${studentId} )",
                "<when test='startDateTime!=null and startDateTime!=&apos;&apos; '>",
                    "and start_date_time > #{startDateTime}",
                "</when>",
            "</script>",
    })
    public abstract List<CourseSchedulingDetail> getSchedulingForStudentList(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("studentId") Long studentId, @Param("startDateTime")LocalDateTime startDateTime);


    @Select({"<script>",
            "select * from course_scheduling_detail ",
            "where",
            "`year_part` = ${yearPart} and `quarter` = ${quarterNum} and deleted = 0 and lesson_type = ${lessonType} and grade_id = ${gradeId}",
            "<when test='startDateTime!=null and startDateTime!=&apos;&apos; '>",
                "and start_date_time > #{startDateTime}",
            "</when>",
            "ORDER BY start_date_time asc",
            "</script>"
    })
    public abstract List<CourseSchedulingDetail> getSchedulingForGradeId(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("gradeId") Long gradeId,@Param("lessonType") Integer lessonType, @Param("startDateTime")String startDateTime);


    @Select("select * from (SELECT course_scheduling_detail.*,grade.year_class_id FROM course_scheduling_detail LEFT JOIN grade ON grade.id = course_scheduling_detail.grade_id) as t   ${ew.customSqlSegment} ")
    public abstract IPage<CourseSchedulingDetail> calendarList(IPage<?> page,@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({"<script>",
            "SELECT t1.id,t1.school_id,t1.lesson_type,t1.lesson_num,t2.subjects_id,t2.grade_id,t2.student_id,t1.year_part,t1.`quarter` AS quarterNum,t2.year_class_id ",
            "FROM course_scheduling_detail AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.grade_id = t2.grade_id AND t1.deleted = t2.deleted ",
            "WHERE",
            "t1.grade_id = ${gradeId} ",
            "AND t2.student_id = ${studentId} ",
            "AND t1.deleted = 0 ",
            "AND t2.lesson_type = 1 ",
            "AND t1.end_date_time &lt; now() ",
            "AND t2.`status` = 1 ",
            "AND t1.lesson_num NOT IN ( ",
                "SELECT lesson_num ",
                "FROM ",
                "lack_course_log AS t3 ",
                "WHERE ",
                "t2.subjects_id = t3.subjects_id  ",
                "AND t1.deleted = t3.deleted  ",
                "AND t2.student_id = t3.student_id ",
                "AND t2.quarter_num = t3.`quarter_num` ",
                "AND t2.year_part = t3.`year_part` ",
                "AND t2.lesson_type = t3.`lesson_type` ",
                // 已经取消的新生缺课课次，不添加缺课
//                "AND t3.`status` IN (0,1)",
            ") ",
            "AND t1.lesson_num NOT IN ( ",
                "SELECT lesson_num ",
                "FROM ",
                "record_course AS t4 ",
                "WHERE ",
                "t2.subjects_id = t4.subjects_id ",
                "AND t1.deleted = t4.deleted ",
                "AND t2.student_id = t4.student_id ",
                "AND t2.quarter_num = t4.`quarter` ",
                "AND t2.year_part = t4.`year_part` ",
                "AND t2.lesson_type = t4.`lesson_type` ",
            ")",
            "<when test='createdAt!=null and createdAt!=&apos;&apos; '>",
                "AND t1.end_date_time &gt; '${createdAt}'",
            "</when>",
            "GROUP BY t1.id ",
            "</script>"}
    )
    public abstract List<LackCourseLog> resumeClassesLackCourse(@Param("gradeId") Long gradeId,@Param("studentId") Long studentId,@Param("createdAt") String createdAt);

    @Select("SELECT * FROM lack_course_log WHERE student_id = #{studentId} AND lesson_type = #{lessonType} AND subjects_id = #{subjectsId} AND lesson_num = #{lessonNum} AND quarter_num = #{quarterNum} AND year_part = #{yearPart} AND deleted = 0 LIMIT 1")
    public abstract LackCourseLog getLackByStudentId(@Param("studentId") Long studentId,@Param("lessonType") Integer lessonType,@Param("subjectsId") Long subjectsId,@Param("lessonNum") Integer lessonNum,@Param("quarterNum") Integer quarterNum,@Param("yearPart") Integer yearPart);


    /**
     * 获取学生的排课时间(没有调补消缺)
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @param nowDateTime
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.* ",
            "FROM",
            "course_scheduling_detail AS t1",
            "INNER JOIN student_grade AS t2 ON t1.grade_id = t2.grade_id ",
            "AND t2.STATUS = 1 ",
            "AND t2.reading_status = 1 ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.quarter_num = ${quarterNum} ",
            "AND t2.student_id = ${studentId} ",
            "AND t2.deleted = 0",
            "and t2.lesson_type =${lessonType}",
            "LEFT JOIN repair_course AS t3 ON t1.id = t3.out_scheduling_detail_id ",
            "AND t3.deleted = 0 ",
            "AND t3.course_type = 4 ",
            "WHERE",
            "t1.start_date_time &gt;= '${nowDateTime}' ",
            "AND t3.id IS NULL ",
            "GROUP BY",
            "t2.grade_id,",
            "t1.id",
            "</script>"})
    public List<CourseSchedulingDetail> getSchedulingListForDate(@Param("yearPart")Integer yearPart,
                                                          @Param("quarterNum")Integer quarterNum,
                                                          @Param("studentId")Long studentId,
                                                          @Param("lessonType")Integer lessonType,
                                                          @Param("nowDateTime")String nowDateTime
    );


    @Select({"<script>",
            "SELECT",
                    "*,(",
                    "full_count - student_count ",
                    ") AS under_count ",
                    "FROM",
                    "(",
                        "SELECT",
                          "t01.*,",
                          "count(*) AS student_count ",
                        "FROM",
                          "(",
                          "SELECT",
                            "t1.`id` AS `id`,",
                            "t1.`school_id`,",
                            "t1.`grade_id`,",
                            "t1.`grade_name`,",
                            "t1.`teacher_id`,",
                            "t1.`teacher_name`,",
                            "t1.`school_date`,",
                            "t1.`start_date_time`,",
                            "t1.`end_date_time`,",
                            "t1.`time_end`,",
                            "t1.`time_start`,",
                            "t1.`deleted`,",
                            "t1.`year_part`,",
                            "t1.`quarter`,",
                            "t1.`subjects_id`,",
                            "t1.`subjects_name`,",
                            "t1.`lesson_type`,",
                            "t3.full_count ",
                          "FROM",
                            "`course_scheduling_detail` AS t1",
                            "INNER JOIN grade AS t3 ON t1.grade_id = t3.id ",
                          "WHERE",
                            "t1.`deleted` = 0 ",
                            "AND t1.school_id = ${schoolId} ",
                            "AND t1.school_date = #{schoolDate} ",
                            "AND t1.subjects_id = ${subjectsId} ",
                            "AND t1.lesson_type = ${lessonType} ",
                            "AND t1.`status` = 0 ",
                            "AND ((#{startDateTime} BETWEEN t1.start_date_time and t1.end_date_time) or (t1.start_date_time &gt;=#{startDateTime} and t1.start_date_time &lt;= #{endDateTime}))",
                            "GROUP BY",
                            "t1.grade_id ",
                          ") AS t01",
                          "INNER JOIN `student_grade` AS t02 ON t01.grade_id = t02.grade_id ",
                          "AND t02.reading_status = 1 ",
                          "AND t02.deleted = 0 ",
                          "AND t02.`status` = 1 ",
                        "GROUP BY",
                          "t01.grade_id ",
                        "HAVING",
                          "((t01.full_count &lt;&gt; 0  AND full_count &lt;= student_count) or (#{startDateTime} BETWEEN t01.start_date_time and t01.end_date_time) or (t01.id = ${schedulingDetailId}) or (t01.start_date_time &gt;=#{startDateTime} and t01.start_date_time &lt;= #{endDateTime})) ",
                        "ORDER BY",
                          "start_date_time ASC ",
                    ") AS t",
            "</script>"})
    public List<CourseSchedulingDetail> getBeforSchedulingOne(@Param("schedulingDetailId")Long schedulingDetailId,
                                                        @Param("schoolId")Long schoolId,
                                                        @Param("studentId")Long studentId,
                                                        @Param("subjectsId")Long subjectsId,
                                                        @Param("lessonType")Integer lessonType,
                                                        @Param("schoolDate")String schoolDate,
                                                        @Param("startDateTime")String startDateTime,
                                                        @Param("endDateTime")String endDateTime);


    @Update({
            "<script>",
            "UPDATE course_scheduling_detail",
            "SET deleted = 1",
            "WHERE grade_id IN (",
            "    SELECT id",
            "    FROM grade",
            "    WHERE",
            "        year_part = ${yearPart}",
            "        AND quarter_num = ${quarterNum}",
            "        AND subjects_id = ${subjectsId}",
            "        AND lesson_type = ${lessonType}",
            "        AND school_id = ${schoolId}",
            "        AND add_type = 2",
            "        AND deleted = 0",
            "        AND id NOT IN (",
            "            SELECT id",
            "            FROM grade",
            "where",
            "    (",
            "        <foreach collection='timeConditions' item='condition' separator=' OR '>",
            " (" ,
            "time_start = #{condition.timeStart} AND time_end =#{condition.timeEnd}",
            "            <choose>",
            "                <when test='condition.schedulingType == \"A类\"'>",
            "                    AND week_dates IN (1, 2, 3, 4, 5)",
            "                </when>",
            "                <when test='condition.schedulingType == \"B类\"'>",
            "                    AND week_dates IN (0, 6)",
            "                </when>",
            "            </choose>",
            "            AND scheduling_type = #{condition.schedulingType}",
            ") ",
            "        </foreach>",
            "    )",
            "                AND year_part = ${yearPart}",
            "                AND quarter_num = ${quarterNum}",
            "                AND subjects_id = ${subjectsId}",
            "                AND lesson_type = ${lessonType}",
            "                AND school_id = ${schoolId}",
            "                AND add_type = 2",
            "                AND deleted = 0",
            "        )",
            "    )",
            "</script>"
    })
    boolean deleteCourseSchedulingDetail(
            @Param("timeConditions") List<GradeCustomGrade> timeConditions,
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") Long subjectsId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolId") Long schoolId);


    @Update({
            "<script>",
            "UPDATE course_scheduling_detail",
            "SET deleted = 1",
            "WHERE grade_id IN (",
            "    SELECT id",
            "    FROM grade",
            "    WHERE",
            "        year_part = #{yearPart}",
            "        AND quarter_num = #{quarterNum}",
            "        AND subjects_id = #{subjectsId}",
            "        AND lesson_type = #{lessonType}",
            "        AND school_id = #{schoolId}",
            "        AND add_type = 2",
            "        AND deleted = 0",
            "        AND (scheduling_type = 'A类' OR scheduling_type = 'B类')",
            ")",
            "</script>"
    })
    boolean deleteAllCourseSchedulingDetail(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") Long subjectsId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolId") Long schoolId);


}
